/*******************************************************************************
 * This file is part of blueprint4j www.blueprint4j.org.
 * 
 *     blueprint4j is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU Lesser General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     blueprint4j is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of
 *     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *     GNU Lesser General Public License for more details.
 * 
 *     You should have received a copy of the GNU Lesser General Public License
 *     along with blueprint4j. If not, see <http://www.gnu.org/licenses/>.
 ******************************************************************************/
package blueprint4j.db;

import java.sql.*;
import java.util.Date;

import blueprint4j.utils.*;

import java.io.*;
import java.text.SimpleDateFormat;

public class DataBaseUtilsFireBird extends DataBaseUtils {

	public static final String PATH_TO_FIREBIRD_SERVER = Settings.getString("database.firebird.server.path","C:/Program Files/Firebird/Firebird_1_5/bin/isql.exe");
	public static final String FIREBIRD_SERVER_USER = Settings.getString("database.firebird.server.user","sysdba");
	public static final String FIREBIRD_SERVER_PASS = Settings.getString("database.firebird.server.pass","masterkey");

	private java.text.SimpleDateFormat simple_date_format = new java.text.SimpleDateFormat( "yyyy-MM-dd" );
	private java.text.SimpleDateFormat simple_time_format = new java.text.SimpleDateFormat( "HH:mm:ss" );
	private java.text.SimpleDateFormat simple_date_and_time_format = new java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );

	private final static long ID_NUMBER_GAP=100;
	private static long next_id_number=0;
	private static long next_id_block=0;

	public SimpleDateFormat getDateTimeFormat() {
		return simple_date_and_time_format;
	}
	
	public SimpleDateFormat getTimeFormat() {
		return simple_time_format;
	}

	public SimpleDateFormat getDateFormat() {
		return simple_date_format;
	}

	/**
	 * This method reads the next index field from the index field table.
	 */
	public Long getNextIndexField(Entity entity)
	throws SQLException {
		if (next_id_number==next_id_block) {
			Statement statement = DBTools.getLocalConnection(entity.getConnection()).createStatement();
			ResultSet rs = statement.executeQuery("select max(index_field) from " + IndexTable.TABLE_NAME);
			long index_field = 1;
			if (rs.next()) {
				index_field = rs.getLong(1);
			} else {}
			statement.execute("insert into " + IndexTable.TABLE_NAME + "(index_field,tablename) values ("+(index_field+1)+",'" + entity.getTableName() + "')");
			next_id_number = index_field*ID_NUMBER_GAP;
			next_id_block=next_id_number+ID_NUMBER_GAP;
			statement.close();
		}
		return new Long(next_id_number++);
	}

	/**
	 * Returns the sql for the generator store proc
	 */
	public void setBeforeInsert(DBConnection connection,FieldAutoId id)
	throws SQLException {
		Statement smt = connection.createStatement();
		ResultSet gen_id = smt.executeQuery(
		                      "select GEN_ID(gen_"+
		                      id.getEntity().getTableName()+ ",1) from "+
		                      "RDB$DATABASE"
		                   );
		if (!gen_id.next()) {
			id.set(new Long(1));
		} else {
			id.set(new Long(gen_id.getLong(1)));
		}
	}

	public void loadIdAfterInsert(DBConnection connection,FieldId id) throws SQLException {}

	public void createIndexTable(Statement st)
	throws SQLException {
		st.execute("create table d_index_table(index_field bigint not null,tablename varchar(64) not null,primary key(index_field))");
	}

	/**
	* Convert a Date to SQL leave out the time
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_format.format(date) + "'";
	}

	/**
	* Convert a Time to SQL leave out the date
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_time_format.format(date) + "'";
	}
	
	public String castTime(String date) {
		return "cast("+date+" AS TIME)";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value without time
	*/
	public String currentDateSQLMethod() {
		return "CURRENT_DATE";
	}

	public String currentTimeSQLMethod() {
		return "CURRENT_TIME";
	}

	/**
	* Convert a Date to SQL including time
	*
	* @param date the DateTime to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_and_time_format.format(date) + "'";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value with time
	*/
	public String currentDateTimeSQLMethod() {
		return "CURRENT_TIMESTAMP";
	}

	/**
	* Get the SQL command for top or first
	*
	* @param number The number of rows you would like back from the select.
	* @return Returns the SQL string command to limit the number of rows returned in a system
	* Note Mysql Can also retrieve the last amount,SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
	*/
	public String sqlFirstString(String sql, int number) {
		if (sql.toUpperCase().indexOf("SELECT")!=-1) {
			return "select first " + number + " " + sql.substring(sql.toUpperCase().indexOf("SELECT")+"SELECT".length());
		} else {
			Log.debug.out("INCORRECT SQL LIMIT   = IN ["+sql+"] NO SELECT FOUNDM","");
			return null;
		}
	}

	/**
	* Converts a string value to a suitable value for an SQL statement
	*
	* @param source The value of the string
	* @return A converted string ready for use in SQL
	*/
	public String convertStringForSQL(String source) {
		return convertStringForSQLAsStatic(source);
	}

	private static String convertStringForSQLAsStatic(String source) {
		if (source == null) return null;
		return "'" + convertStringForSQLBeforeQuotes(source) + "'";
	}

	private static String convertStringForSQLBeforeQuotes(String source) {
		if (source == null) return null;
		return Utils.replaceAll(source, "'", "''");
	}

	/**
	* Converts a string after is has been returned from the database
	*
	* @param source The value of the string from the database
	* @return A converted string converted from the SQL syntax
	*/
	public String convertStringFromSQL(String source) {
		if (source == null) return null;
		return source;
	}

	public String dateSUB_HOUR(Date date,int hours) {
		return " CAST ("+convertDateTimeToSQLString(date)+" AS TIMESTAMP)-("+(hours/24.0) + ")" ;
	}

	public String dateSUB_MIN(Date date,int min) {
		return " CAST ("+convertDateTimeToSQLString(date)+" AS TIMESTAMP)-("+(min/(24.0*60)) + ")" ;
	}

	public String dateSUB_SEC(Date date,int sec) {
		return " CAST ("+convertDateTimeToSQLString(date)+" AS TIMESTAMP)-("+(sec/(24.0*60*60)) + ")" ;
	}
	
	public boolean mayUpdateFromResultSet(DBConnection dbcon)  throws SQLException{
		return false;
	}

	public void executeKeepAliveSQL(DBConnection connection)
	throws SQLException {
		Statement smt = connection.createStatement();
		smt.executeQuery("select 1 = 1");
		smt.close();
	}

	public void executeOptimizeTable(DBConnection connection,String table_name)
	throws SQLException {}

	public void dropIndex(DBConnection connection,String table_name,String index_name)
	throws SQLException,DataException {
		Statement smt = connection.createStatement();
		Log.trace.out("DATABASE UPDATE INDEX","DROP INDEX ["+index_name+"] ON TABLE ["+table_name+"]");
		connection.execute("drop index " + index_name);
		smt.close();
	}

	/**
	 * Create the table sql for FireBird
	 * Has not auto increment number so a store proc will have to be created
	 */
	public void createTable(DBConnection connection,String table_name,Field table_fields[])
	throws SQLException {
		String create_sql = "create table "+ table_name + "( ";
		boolean create_trigger = false;
		for (int i =0;i < table_fields.length;i++) {
			if (table_fields[i] instanceof FieldAutoId) {
				create_sql += table_fields[i].getName() + " integer not null,";
				create_trigger = true;
			} else {
				create_sql += table_fields[i].getName() + " " + getSQLType(table_fields[i]) + ",";
			}
		}
		create_sql = create_sql.substring(0,create_sql.length()-1);
		create_sql += ")";
		Log.trace.out("Updating Firebird Database",create_sql);
		connection.execute(create_sql);
		if (create_trigger) {
			try {
				Log.trace.out("Updating Firebird Database","create generator gen_"+table_name);
				connection.execute("create generator gen_"+table_name);
			} catch (SQLException slqe) {}
			// RESET GEN TO 0 INCASE SUCH A GENERATOR DID EXIST
			connection.execute("set generator gen_"+table_name + " to 0");
		}
	}

	public String getSQLType(FieldAutoId field){return "bigint AUTO_INCREMENT";}

	public String getSQLType(FieldBigDecimal field) {
		return "double precision";
	}
	public String getSQLType(FieldBigInteger field) {
		return "integer";
	}
	public String getSQLType(FieldBoolean field) {
		return "char(1)";
	}
	public String getSQLType(FieldCharacter field) {
		return "char(1)";
	}
	public String getSQLType(FieldColor field) {
		return "char(6)";
	}
	public String getSQLType(FieldCurrency field) {
		return "decimal(10,2)";
	}
	public String getSQLType(FieldDate field) {
		return "date";
	}
	public String getSQLType(FieldDateTime field) {
		return "timestamp";
	}
	public String getSQLType(FieldDouble field) {
		return "double precision";
	}
	public String getSQLType(FieldInteger field) {
		return "smallint";
	}
	public String getSQLType(FieldLong field) {
		return "integer";
	}
	public String getSQLType(FieldString field) {
		return "varchar("+field.getLength()+")";
	}
	public String getSQLType(FieldTime field) {
		return "time";
	}
	public String getSQLType(FieldText field) {
		return "varchar(2047)";
	}
	public String getSQLType(FieldImage field) {
		return "varchar(8192)";
	}
	public String getSQLType(FieldProperties field) {
		return "varchar(2047)";
	}
	public String getSQLType(FieldBlob field) {
		return "blob";
	}

	public void addField(DBConnection dbcon,Field field)
	throws SQLException {
		if (!field.mayFieldBeNull()) {
			dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field) + " not null");
		} else {
			dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field));
		}
	}

	public void modifyTableField(DBConnection connection,Field field)
	throws SQLException {
		Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " alter column " + field.getName() + " TYPE " + getSQLType(field));
		connection.execute("alter table " + field.getEntity().getTableName() + " alter column " + field.getName() + " TYPE " + getSQLType(field));
	}

	public void modifyTableField(DBConnection connection,String old_name,Field field)
	throws SQLException {
		throw new SQLException("NOT CODED YET");
	}

	public String getDropField(String name,String tablename) {
		return "alter table " + tablename + " drop column " + name;
	}

	public Boolean isCorrectType(Field field,int type,int size)
	throws DataException {
		if (field instanceof FieldBigInteger) {
			if (type == Types.BIGINT || type == Types.INTEGER) {
				return new Boolean(true);
			} else {
				Log.trace.out("Updating Database","FIELD ["+field.getName()+"] ["+field.getEntity().getTableName()+"] BIGINTEGER MISMATCH GOT ["+type+"] EXPECTING ["+Types.BIGINT+"]");
				return new Boolean(false);
			}
		} else if (field instanceof FieldLong) {
			if (type == Types.BIGINT || type == Types.INTEGER) {
				return new Boolean(true);
			} else {
				Log.trace.out("Updating Database","FIELD ["+field.getName()+"] ["+field.getEntity().getTableName()+"] LONG MISMATCH GOT ["+type+"] EXPECTING ["+Types.BIGINT+"]");
				return new Boolean(false);
			}
		} else if (field instanceof FieldInteger) {
			if (type == Types.INTEGER || type == Types.SMALLINT){
				return new Boolean(true);
			} else {
				Log.trace.out("Updating Database","FIELD ["+field.getName()+"] ["+field.getEntity().getTableName()+"] INTEGER MISMATCH GOT ["+type+"] EXPECTING ["+Types.INTEGER+"]");
				return new Boolean(false);
			}
		} else if (field instanceof FieldBlob) {
			if (type == Types.BLOB || type == Types.LONGVARBINARY) {
				return new Boolean(true);
			} else {
				Log.trace.out("Updating Database","FIELD ["+field.getName()+"] ["+field.getEntity().getTableName()+"] BLOB MISMATCH GOT ["+type+"] EXPECTING ["+Types.BLOB+"]");
				return new Boolean(false);
			}
		} else {
			return super.isCorrectType(field,type,size);
		}
	}

	public void setupDatabaseServer(DBConnection connect) throws SQLException {}

	public boolean createDatabase(String path,String dbname,String username,String password)  {
		try {
			new File(path).mkdir();
			File dbfile = new File(path + "/"+dbname);
			if (dbfile.exists()) {
				return true;
			}
			java.io.InputStream stream = ClassLoader.getSystemResourceAsStream("db/firebird/example");
			java.io.FileOutputStream outstream = new java.io.FileOutputStream(dbfile);
			Utils.readWrite(stream,outstream);
			outstream.close();
			return true;
		} catch (Exception exception) {
			exception.printStackTrace();
			Log.debug.out(exception);
			return false;
		}
	}

	/**
	 * Transposes this file for bulk loading through the
	 * opensource tool FBExport
	 * All lines must be started and ended by a " and any "
	 * must be replaced by a double " ("")
	 */
	private File transposeForBulkLoad(File infile,int coltype[])
	throws IOException {
		LineNumberReader reader = new LineNumberReader(
		                             new InputStreamReader(
		                                new FileInputStream(infile)
		                             )
		                          );
		File tmp_file = File.createTempFile("bulk_import","firebird");
		FileOutputStream stream = new FileOutputStream(tmp_file);
		stream.write(0);
		stream.write(125);
		stream.write(coltype.length);
		for (int i =0;i < coltype.length;i++) {
			stream.write(coltype[i]);
		}
		for (String line = reader.readLine();line != null;line = reader.readLine()) {
			VectorString vs = new VectorString(line,",");
			for (int i =0;i < vs.size();i++) {
				if (vs.get(i) == null) {
					stream.write(0);
				} else {
					stream.write(vs.get(i).length());
					stream.write(vs.get(i).getBytes());
				}
			}
		}
		stream.close();
		return tmp_file;
	}

	public int[] getColTypeForBulkLoad(ResultSetMetaData meta)
	throws SQLException {
		VectorInteger vi =new VectorInteger();
		for (int i =1;i <= meta.getColumnCount();i++) {
			switch (meta.getColumnType(i)) {
			case Types.DATE : vi.add(2);break;
			case Types.TIME : vi.add(2);break;
			case Types.TIMESTAMP : vi.add(2);break;
			case Types.CHAR : vi.add(5);break;
			case Types.VARCHAR : vi.add(5);break;
			case Types.SMALLINT : vi.add(6);break;
			case Types.INTEGER : vi.add(7);break;
			case Types.BIGINT : vi.add(8);break;
			case Types.FLOAT : vi.add(9);break;
			case Types.DOUBLE : vi.add(10);break;
			case Types.DECIMAL : vi.add(10);break;
			default: throw new DataException("COULD NOT IDENTIFY SQL TYPE ["+meta.getColumnType(i)+"]");
			}
		}
		return vi.toArray();
	}

	public void bulkLoadFile(DBConnection dbcon,String tablename,File file,String delimiter)
	throws SQLException {
		try {
			// LOAD BULK LOAD APPLICATION: NOTE HAS JUST BEEN COMPILED FOR WINDOWS
			File bulk_loader = new File(dbcon.getPath()+"/FBExport.exe");
			if (!bulk_loader.exists()) {
				java.io.InputStream stream = ClassLoader.getSystemResourceAsStream("db/firebird/FBExport.exe");
				java.io.FileOutputStream outstream = new java.io.FileOutputStream(bulk_loader);
				Utils.readWrite(stream,outstream);
				outstream.close();
			}
			Statement smt = dbcon.createStatement();
			File load_file = transposeForBulkLoad(
			                    file,
			                    getColTypeForBulkLoad(
			                       smt.executeQuery("select * from " + tablename).getMetaData()
			                    )
			                 );
			smt.close();
			File dbfile = new File(dbcon.getPath() + "/"+dbcon.getDatabaseName());
			String command = bulk_loader.getAbsolutePath() + " " +
			                 "-I "+
			                 "-H " + dbcon.getHost() + " " +
			                 "-D " + dbfile.getAbsolutePath() + " " +
			                 "-F " + load_file.getAbsolutePath() + " " +
			                 "-U " + dbcon.getUsername() + " " +
			                 "-P " + dbcon.getPassword() + " " +
			                 "-V " + tablename;
			command = Utils.replaceAll(command,"\\","/");
			Log.trace.out("BULK LOADING FIREBIRD",command);
			Process proc = Runtime.getRuntime().exec(command);
			boolean finished = false;
			for (;!finished && ThreadSchedule.isStillAlive();) {
				try {
					String message = Utils.readFromStream(proc.getInputStream());
					String error = Utils.readFromStream(proc.getErrorStream());
					if (message.length() > 0) {
						Log.trace.out("BULK LOADER FIREBIRD MESSAGE",message);
					}
					if (error.length() > 0) {
						Log.trace.out("BULK LOADER FIREBIRD MESSAGE",error);
					}
					proc.exitValue();
					finished = true;
				} catch (Exception ex) {}
			}
			Log.trace.out("BULK LOADING FIREBIRD","EXIT VALUE " + proc.exitValue());
			if (proc.exitValue() != 0) {
				throw new DataException("BULK LOADING FAILED. BULK LOADER EXITED WITH VALUE ["+proc.exitValue()+"]");
			}
			load_file.delete();
		} catch (Throwable th) {
			throw new DataException(th);
		}
	}

	public void exportData(DBConnection dbcon,String select,File bulkfile)
	throws SQLException {
		throw new DataException("Not implemented yet");
	}

	public void backupDB(DBConnection dbcon,File file) throws Throwable {}
	public void restoreDB(DBConnection dbcon,File file) throws Throwable {}
}

